﻿using System;
using System.Collections.Generic;
using System.Data;

namespace MyLib.Data {
	/// <summary>
	/// 
	/// </summary>
	public class SettingsProvider : DbContextProvider {
		/// <summary>
		/// 
		/// </summary>
		/// <param name="category"></param>
		/// <returns></returns>
		public IDictionary<string, string> LoadByCategory(int category) {
			var sql =
@"select `key`, `value` from my_settings where category = #category#";
			var paramCategory = new DbParameter("category", DbParameterType.Integer, category);

			using (var session = SessionFactory.CreateReadOnlySession()) {
				var dt = session.ExecuteQuery(sql, System.Data.CommandType.Text, paramCategory);

				var settings = new Dictionary<string, string>(dt.Rows.Count);
				foreach (DataRow row in dt.Rows) {
					settings[row.GetString("key")] = row.GetString("value");
				}

				return settings;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="category"></param>
		/// <param name="key"></param>
		/// <returns></returns>
		public string LoadByKey(int category, string key) {
			var sql =
@"select `value` from my_settings where `category` = #category# and `key` = #key#";
			var paramCategory = new DbParameter("category", DbParameterType.Integer, category);
			var paramKey = new DbParameter("key", DbParameterType.String, key);

			using (var session = SessionFactory.CreateReadOnlySession()) {
				var value = session.ExecuteScalar<string>(sql, System.Data.CommandType.Text, paramCategory, paramKey);
				if (string.IsNullOrEmpty(value))
					return string.Empty;
				else
					return value;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="category"></param>
		public void SaveByCategory(int category, IDictionary<string, string> settings) {
			var sql0 =
@"delete from my_settings where category = #category# and `key` = #key#";
			var sql1 =
@"insert into my_settings (`key`, `value`, `category`) values (#key#, #value#, #category#)";

			var paramKey = new DbParameter("key", DbParameterType.String, null);
			var paramValue = new DbParameter("value", DbParameterType.String, null);
			var paramCategory = new DbParameter("category", DbParameterType.Integer, category);

			using (var session = SessionFactory.CreateSession()) {
				foreach (var se in settings) {
					paramKey.Value = se.Key;
					paramValue.Value = se.Value;

					session.ExecuteUpdate(sql0, CommandType.Text, paramCategory, paramKey);

					if (!string.IsNullOrEmpty(se.Value)) {
						session.ExecuteUpdate(sql1, CommandType.Text, paramKey, paramValue, paramCategory);
					}
				}
				session.Commit();
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="category"></param>
		/// <param name="key"></param>
		/// <param name="value"></param>
		public void SaveByKey(int category, string key, string value) {
			var sql0 =
@"delete from my_settings where category = #category# and `key` = #key#";
			var sql1 =
@"insert into my_settings (`key`, `value`, `category`) values (#key#, #value#, #category#)";

			var paramKey = new DbParameter("key", DbParameterType.String, key);
			var paramValue = new DbParameter("value", DbParameterType.String, value);
			var paramCategory = new DbParameter("category", DbParameterType.Integer, category);

			using (var session = SessionFactory.CreateSession()) {
				session.ExecuteUpdate(sql0, CommandType.Text, paramCategory, paramKey);

				if (!string.IsNullOrEmpty(value)) {
					session.ExecuteUpdate(sql1, CommandType.Text, paramKey, paramValue, paramCategory);
				}
				session.Commit();
			}
		}
	} // end of SettingsProvider.
}
